import pandas as pd
import matplotlib.pyplot as plt
import matplotlib.ticker as mticker
import numpy as np
import warnings
warnings.filterwarnings('ignore')

# Set global plot style
plt.rcParams['figure.figsize'] = (12, 6)
plt.rcParams['axes.spines.top'] = False
plt.rcParams['axes.spines.right'] = False
plt.rcParams['font.family'] = 'DejaVu Sans'

# ── File paths ────────────────────────────────────────────────────────────────
PATH = r"C:\Users\Lenovo\Downloads"

CLA_FILE    = PATH + r"\CLA-Database-Raw-Data-Public-2025-FIN.xlsx"
FDI_FILE    = PATH + r"\FDIData_Nov2025.xlsx"
TRADE_FILE  = PATH + r"\TradeData_Nov2025.xlsx"

# ── Load CLA dataset ──────────────────────────────────────────────────────────
df_cla_raw = pd.read_excel(CLA_FILE, sheet_name='2025', header=0)
df_cla_raw.columns = df_cla_raw.iloc[0]
df_cla = df_cla_raw.iloc[1:].reset_index(drop=True)
df_cla.columns = [str(c).strip() for c in df_cla.columns]
df_cla['Year'] = pd.to_numeric(df_cla['Year'], errors='coerce')
df_cla['Loan (USD M)'] = pd.to_numeric(df_cla['Loan (USD M)'], errors='coerce')

# ── Load FDI dataset ──────────────────────────────────────────────────────────
df_fdi_flow_raw = pd.read_excel(FDI_FILE, sheet_name='CN_Africa_FDI_Flow', header=0)
df_fdi_flow_raw.columns = df_fdi_flow_raw.iloc[0]
df_fdi_flow = df_fdi_flow_raw.iloc[1:].reset_index(drop=True)
df_fdi_flow.rename(columns={df_fdi_flow.columns[0]: 'Year'}, inplace=True)
df_fdi_flow['Year'] = pd.to_numeric(df_fdi_flow['Year'], errors='coerce')

df_fdi_graph_raw = pd.read_excel(FDI_FILE, sheet_name='Graph', header=0)
df_fdi_graph_raw.columns = df_fdi_graph_raw.iloc[0]
df_fdi_graph = df_fdi_graph_raw.iloc[1:].reset_index(drop=True)
df_fdi_graph.columns = ['Year', 'China_FDI_bn', 'US_FDI_bn']
df_fdi_graph['Year'] = pd.to_numeric(df_fdi_graph['Year'], errors='coerce')
df_fdi_graph['China_FDI_bn'] = pd.to_numeric(df_fdi_graph['China_FDI_bn'], errors='coerce')
df_fdi_graph['US_FDI_bn'] = pd.to_numeric(df_fdi_graph['US_FDI_bn'], errors='coerce')

# ── Load Trade dataset ────────────────────────────────────────────────────────
df_trade_raw = pd.read_excel(TRADE_FILE, sheet_name='Graphs 2', header=0)
df_trade_raw.columns = df_trade_raw.iloc[1]
df_trade = df_trade_raw.iloc[2:].reset_index(drop=True)
df_trade.columns = ['Year', 'CN_EX', 'CN_IM', 'CN_Total', 'US_EX', 'US_IM', 'US_Total', 'extra']
for col in df_trade.columns:
    df_trade[col] = pd.to_numeric(df_trade[col], errors='coerce')
df_trade = df_trade.dropna(subset=['Year'])

print("✓ CLA loaded:", df_cla.shape)
print("✓ FDI flow loaded:", df_fdi_flow.shape)
print("✓ FDI graph loaded:", df_fdi_graph.shape)
print("✓ Trade loaded:", df_trade.shape)

# ── Isolate ICT loans ─────────────────────────────────────────────────────────
df_ict = df_cla[df_cla['Sector'] == 'Information and Communication Technology'].copy()
print(f"\n✓ ICT loans: {len(df_ict)} records | Total value: ${df_ict['Loan (USD M)'].sum():,.1f}M")

✓ CLA loaded: (1319, 13)
✓ FDI flow loaded: (42, 60)
✓ FDI graph loaded: (22, 3)
✓ Trade loaded: (0, 8)

✓ ICT loans: 160 records | Total value: $15,259.1M

# ICT loans aggregated by year
ict_by_year = df_ict.groupby('Year')['Loan (USD M)'].sum().reset_index()
ict_by_year = ict_by_year[ict_by_year['Year'].between(2000, 2023)]

fig, ax = plt.subplots(figsize=(13, 5))

ax.bar(ict_by_year['Year'], ict_by_year['Loan (USD M)'],
       color='#c0392b', alpha=0.85, edgecolor='white', linewidth=0.5)

ax.set_title("Chinese ICT Loan Commitments to Africa (2000–2023)",
             fontsize=14, fontweight='bold', pad=15)
ax.set_xlabel("Year", fontsize=11)
ax.set_ylabel("Loan Value (USD Million)", fontsize=11)
ax.yaxis.set_major_formatter(mticker.FuncFormatter(lambda x, _: f'${x:,.0f}M'))
ax.set_xticks(range(2000, 2024, 2))
ax.tick_params(axis='x', rotation=45)

# Annotate key peaks
for _, row in ict_by_year[ict_by_year['Loan (USD M)'] > 400].iterrows():
    ax.annotate(f"${row['Loan (USD M)']:.0f}M",
                xy=(row['Year'], row['Loan (USD M)']),
                xytext=(0, 6), textcoords='offset points',
                ha='center', fontsize=8, color='#c0392b')

ax.axvline(x=2015, color='gray', linestyle='--', linewidth=1, alpha=0.6)
ax.text(2015.2, ict_by_year['Loan (USD M)'].max() * 0.9,
        'FOCAC 2015\n($60bn pledge)', fontsize=8, color='gray')

ax.axvline(x=2018, color='steelblue', linestyle='--', linewidth=1, alpha=0.6)
ax.text(2018.2, ict_by_year['Loan (USD M)'].max() * 0.75,
        'FOCAC 2018\n($60bn pledge)', fontsize=8, color='steelblue')

plt.tight_layout()
plt.savefig('fig1_ict_lending_timeline.png', dpi=150, bbox_inches='tight')
plt.show()
print(f"Total ICT loan value: ${ict_by_year['Loan (USD M)'].sum():,.1f}M")

Total ICT loan value: $15,259.1M

ict_by_country = (df_ict.groupby('Country')['Loan (USD M)']
                  .sum()
                  .sort_values(ascending=True)
                  .tail(15))

fig, ax = plt.subplots(figsize=(11, 7))

bars = ax.barh(ict_by_country.index, ict_by_country.values,
               color='#c0392b', alpha=0.8, edgecolor='white')

# Highlight case-study countries
highlights = {'Togo': '#e67e22', 'South Africa': '#2980b9', 'Ethiopia': '#27ae60'}
for bar, country in zip(bars, ict_by_country.index):
    if country in highlights:
        bar.set_color(highlights[country])
        bar.set_alpha(1.0)

ax.set_title("Top 15 African Countries by Chinese ICT Loan Value (2000–2023)",
             fontsize=13, fontweight='bold', pad=12)
ax.set_xlabel("Total Loan Value (USD Million)", fontsize=11)
ax.xaxis.set_major_formatter(mticker.FuncFormatter(lambda x, _: f'${x:,.0f}M'))

# Add value labels
for bar in bars:
    ax.text(bar.get_width() + 5, bar.get_y() + bar.get_height()/2,
            f"${bar.get_width():,.0f}M",
            va='center', ha='left', fontsize=8)

# Legend for highlights
from matplotlib.patches import Patch
legend_elements = [Patch(facecolor='#e67e22', label='Togo (case study)'),
                   Patch(facecolor='#2980b9', label='South Africa (case study)'),
                   Patch(facecolor='#27ae60', label='Ethiopia (case study)'),
                   Patch(facecolor='#c0392b', alpha=0.8, label='Other countries')]
ax.legend(handles=legend_elements, loc='lower right', fontsize=9)

plt.tight_layout()
plt.savefig('fig2_ict_by_country.png', dpi=150, bbox_inches='tight')
plt.show()

fdi = df_fdi_graph.dropna(subset=['Year', 'China_FDI_bn'])
fdi = fdi[fdi['Year'] >= 2003]

fig, ax = plt.subplots(figsize=(13, 5))

ax.plot(fdi['Year'], fdi['China_FDI_bn'],
        color='#c0392b', linewidth=2.5, marker='o', markersize=5, label='China FDI Flow to Africa')
ax.plot(fdi['Year'], fdi['US_FDI_bn'],
        color='#2980b9', linewidth=2.5, marker='s', markersize=5, label='US FDI Flow to Africa', linestyle='--')

ax.fill_between(fdi['Year'], fdi['China_FDI_bn'], alpha=0.08, color='#c0392b')

ax.set_title("China vs. US FDI Flows to Africa (2003–2024)",
             fontsize=14, fontweight='bold', pad=12)
ax.set_xlabel("Year", fontsize=11)
ax.set_ylabel("FDI Flow (USD Billion)", fontsize=11)
ax.yaxis.set_major_formatter(mticker.FuncFormatter(lambda x, _: f'${x:.1f}bn'))
ax.legend(fontsize=10)
ax.axvline(x=2021, color='green', linestyle=':', linewidth=1.5, alpha=0.7)
ax.text(2021.1, fdi['China_FDI_bn'].max() * 0.85,
        'EU Global Gateway\nlaunched (2021)', fontsize=8, color='green')

plt.tight_layout()
plt.savefig('fig3_fdi_comparison.png', dpi=150, bbox_inches='tight')
plt.show()

# Read directly from the raw export and import sheets
df_ex_raw = pd.read_excel(TRADE_FILE, sheet_name='CN_EXtoAfr', header=0)
df_ex_raw.columns = df_ex_raw.iloc[0]
df_ex = df_ex_raw.iloc[1:].reset_index(drop=True)
df_ex.rename(columns={df_ex.columns[0]: 'Year'}, inplace=True)
df_ex['Year'] = pd.to_numeric(df_ex['Year'], errors='coerce')

df_im_raw = pd.read_excel(TRADE_FILE, sheet_name='CN_IMfromAfr', header=0)
df_im_raw.columns = df_im_raw.iloc[0]
df_im = df_im_raw.iloc[1:].reset_index(drop=True)
df_im.rename(columns={df_im.columns[0]: 'Year'}, inplace=True)
df_im['Year'] = pd.to_numeric(df_im['Year'], errors='coerce')

# Pull the pre-calculated totals column from each sheet
df_ex['Total_EX'] = pd.to_numeric(df_ex['Total, US$ mn'], errors='coerce')
df_im['Total_IM'] = pd.to_numeric(df_im['Total, US$ mn'], errors='coerce')

# Merge on Year
trade_clean = pd.merge(
    df_ex[['Year', 'Total_EX']],
    df_im[['Year', 'Total_IM']],
    on='Year', how='inner'
)
trade_clean = trade_clean.dropna(subset=['Year', 'Total_EX', 'Total_IM'])
trade_clean = trade_clean[trade_clean['Year'] >= 2000]

# Convert from USD mn to USD bn
trade_clean['Total_EX_bn'] = trade_clean['Total_EX'] / 1000
trade_clean['Total_IM_bn'] = trade_clean['Total_IM'] / 1000

print("Trade data preview:")
print(trade_clean.head())
print(f"\nYears covered: {int(trade_clean['Year'].min())} – {int(trade_clean['Year'].max())}")

# ── Plot ──────────────────────────────────────────────────────────────────────
fig, ax = plt.subplots(figsize=(13, 5))

ax.stackplot(trade_clean['Year'],
             trade_clean['Total_EX_bn'],
             trade_clean['Total_IM_bn'],
             labels=['China Exports to Africa', 'China Imports from Africa'],
             colors=['#c0392b', '#e74c3c'], alpha=[0.85, 0.5])

ax.set_title("China–Africa Trade Volume (2000–2024)",
             fontsize=14, fontweight='bold', pad=12)
ax.set_xlabel("Year", fontsize=11)
ax.set_ylabel("Trade Value (USD Billion)", fontsize=11)
ax.yaxis.set_major_formatter(mticker.FuncFormatter(lambda x, _: f'${x:.0f}bn'))
ax.legend(loc='upper left', fontsize=10)

plt.tight_layout()
plt.savefig(r"C:\Users\Lenovo\Downloads\fig4_trade_volume.png", dpi=150, bbox_inches='tight')
plt.show()

Trade data preview:
0     Year      Total_EX      Total_IM  Total_EX_bn  Total_IM_bn
8   2000.0   5007.117655   4852.661844     5.007118     4.852662
9   2001.0   5959.776793   4081.154683     5.959777     4.081155
10  2002.0   6918.366806   4608.572201     6.918367     4.608572
11  2003.0  10124.757426   7409.089806    10.124757     7.409090
12  2004.0  13729.951398  13740.248388    13.729951    13.740248

Years covered: 2000 – 2024

case_countries = ['Togo', 'South Africa', 'Ethiopia']
colors = {'Togo': '#e67e22', 'South Africa': '#2980b9', 'Ethiopia': '#27ae60'}

df_cases = df_ict[df_ict['Country'].isin(case_countries)].copy()
df_cases['Year'] = df_cases['Year'].astype(int)

fig, axes = plt.subplots(1, 3, figsize=(15, 5), sharey=False)

for ax, country in zip(axes, case_countries):
    data = (df_cases[df_cases['Country'] == country]
            .groupby('Year')['Loan (USD M)'].sum().reset_index())
    
    if data.empty:
        ax.text(0.5, 0.5, f'No ICT loans\nrecorded for\n{country}',
                ha='center', va='center', transform=ax.transAxes,
                fontsize=12, color='gray')
    else:
        ax.bar(data['Year'], data['Loan (USD M)'],
               color=colors[country], alpha=0.85, edgecolor='white')
        ax.yaxis.set_major_formatter(mticker.FuncFormatter(lambda x, _: f'${x:.0f}M'))
    
    ax.set_title(country, fontsize=13, fontweight='bold', color=colors[country])
    ax.set_xlabel("Year", fontsize=10)
    ax.set_ylabel("Loan Value (USD M)", fontsize=10)
    ax.tick_params(axis='x', rotation=45)

fig.suptitle("Chinese ICT Loans — Case Study Countries", fontsize=14, fontweight='bold', y=1.02)
plt.tight_layout()
plt.savefig('fig5_case_study_loans.png', dpi=150, bbox_inches='tight')
plt.show()

# Print summary stats
for country in case_countries:
    total = df_ict[df_ict['Country'] == country]['Loan (USD M)'].sum()
    count = len(df_ict[df_ict['Country'] == country])
    print(f"{country}: {count} ICT loans | Total: ${total:,.1f}M")

Togo: 4 ICT loans | Total: $133.2M
South Africa: 0 ICT loans | Total: $0.0M
Ethiopia: 7 ICT loans | Total: $3,112.5M

# Curated comparison data (sourced from literature and FOCAC/Global Gateway documents)
categories = [
    'Total Finance\nCommitted ($bn)',
    'ICT-Specific\nLoans ($bn)',
    'Speed of\nDisbursement\n(1=slow, 5=fast)',
    'Regulatory\nConditionality\n(1=low, 5=high)',
    'Local Content\nRequirements\n(1=low, 5=high)',
    'Data Governance\nStandards\n(1=low, 5=high)'
]

# Normalised scores (0–10) for radar / bar comparison
china_scores  = [50.7, 6.2,  4.5, 1.5, 2.0, 1.5]
eu_scores     = [54.0, 2.1,  2.0, 4.5, 3.5, 4.5]

x = np.arange(len(categories))
width = 0.35

fig, ax = plt.subplots(figsize=(13, 6))

bars1 = ax.bar(x - width/2, china_scores, width,
               label="China (DSR / FOCAC 2024)", color='#c0392b', alpha=0.85, edgecolor='white')
bars2 = ax.bar(x + width/2, eu_scores, width,
               label="EU (Global Gateway + Mattei Plan)", color='#2980b9', alpha=0.85, edgecolor='white')

ax.set_title("China DSR vs. EU Global Gateway — Digital Engagement Comparison",
             fontsize=13, fontweight='bold', pad=12)
ax.set_xticks(x)
ax.set_xticklabels(categories, fontsize=9)
ax.set_ylabel("Score / Value", fontsize=11)
ax.legend(fontsize=10)

# Annotate
for bar in bars1:
    ax.text(bar.get_x() + bar.get_width()/2., bar.get_height() + 0.1,
            f'{bar.get_height():.1f}', ha='center', va='bottom', fontsize=8, color='#c0392b')
for bar in bars2:
    ax.text(bar.get_x() + bar.get_width()/2., bar.get_height() + 0.1,
            f'{bar.get_height():.1f}', ha='center', va='bottom', fontsize=8, color='#2980b9')

ax.text(0.5, -0.18,
        "Note: Finance figures in USD bn. Speed/Conditionality/Standards scored 1–5 based on comparative literature.",
        ha='center', transform=ax.transAxes, fontsize=8, color='gray')

plt.tight_layout()
plt.savefig('fig6_eu_vs_china_comparison.png', dpi=150, bbox_inches='tight')
plt.show()

sector_totals = (df_cla.groupby('Sector')['Loan (USD M)']
                 .sum()
                 .sort_values(ascending=True))

colors_sector = ['#c0392b' if s == 'Information and Communication Technology'
                 else '#bdc3c7' for s in sector_totals.index]

fig, ax = plt.subplots(figsize=(11, 7))
bars = ax.barh(sector_totals.index, sector_totals.values,
               color=colors_sector, edgecolor='white', alpha=0.9)

ax.set_title("Chinese Loan Commitments to Africa by Sector (2000–2023)",
             fontsize=13, fontweight='bold', pad=12)
ax.set_xlabel("Total Loan Value (USD Million)", fontsize=11)
ax.xaxis.set_major_formatter(mticker.FuncFormatter(lambda x, _: f'${x:,.0f}M'))

for bar in bars:
    ax.text(bar.get_width() + 20, bar.get_y() + bar.get_height()/2,
            f"${bar.get_width():,.0f}M", va='center', fontsize=8)

from matplotlib.patches import Patch
legend_els = [Patch(facecolor='#c0392b', label='ICT (Digital Silk Road focus)'),
              Patch(facecolor='#bdc3c7', label='Other sectors')]
ax.legend(handles=legend_els, fontsize=9)

plt.tight_layout()
plt.savefig('fig7_sector_breakdown.png', dpi=150, bbox_inches='tight')
plt.show()

pct = sector_totals['Information and Communication Technology'] / sector_totals.sum() * 100
print(f"ICT share of total Chinese lending to Africa: {pct:.1f}%")

ICT share of total Chinese lending to Africa: 8.4%

Dimension	Indicator	China DSR	EU Global Gateway
Infrastructure sovereignty	Vendor diversity, ownership of backbone	Low: Huawei/ZTE dominant	Medium: promotes open RAN, mixed vendors
Data governance	National data laws, cross-border data flows	Low: limited conditionality	High: GDPR-aligned frameworks
Economic agency	Local content, tech transfer, jobs	Medium: some local hiring	Medium: regulatory, less capital
Political leverage	Debt exposure, renegotiation power	High risk: 21% of external debt	Low risk: grant-heavy instruments

A Comparison of Cooperation Models: The Impact of Foreign Digital Investments on African Autonomy¶

Introduction¶

Methodology¶

1. China's Digital Silk Road: The Lending Footprint¶

2. Beyond Loans: FDI and Trade as Indicators of Structural Dependence¶

3. Case Studies: Digital Infrastructure on the Ground¶

3.1 South Africa: Huawei/ZTE vs. BCX/MTN¶

3.2 Togo: Novissi and the Dual Edge of Digital Dependence¶

3.3 Ethiopia: Green Industrial Parks and the Digital-Industrial Nexus¶

4. The EU Global Gateway and the Mattei Plan: A Credible Alternative?¶

5. Assessing Digital Autonomy: A Framework¶

6. Discussion¶

Conclusion¶

References¶